PostgreSQL 服务器编程 saveOrUpdate
1 背景知识
在开发企业级应用软件时，saveOrUpdate 是一种常见的操作。它常用于决定一个SQL语句是插入一个新记录还是更新一个记录。为的就是避免主键，唯一键约束的报错。
1.1 考虑 saveOrUpdate 的安全性
在使用 saveOrUpdate 方法之前，确认此方法能够保护数据的安全性，尤其是在创建-读取-更新-删除（CRUD）企业级应用中。
1.2 实现 saveOrUpdate 的方法
以下是几种实现 saveOrUpdate 的方法
- 使用
JDBC进行定制开发。 - 使用
ORM框架进行开发 。 - 使用
PLSQL实现UPSERT操作。 - 使用 PostgreSQL 数据库自带的
UPSERT操作。 - PLSQL 调用
UPSERT操作。
本章将使用不同方式来实现 saveOrUpdate 。并得出各种方法的优势和劣势。
2 使用 JDBC 进行定制开发
在 Java 中实现 PostgreSQL 数据库的 saveOrUpdate 功能。以下是使用 JDBC 一个完整的示例，演示了如何使用JDBC执行这个操作。
2.1 实现 saveOrUpdate 操作
在不使用ORM框架（如Hibernate）的情况下模拟 saveOrUpdate 功能，您可以手动编写Java代码来检查目标对象是否已存在于数据库中，然后根据实际情况执行插入或更新操作。以下是实现 saveOrUpdate 功能的一个示例：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SaveOrUpdateWithoutORM {
private static final String DB_URL = "jdbc:kingbase://192.168.40.111:54321/test";
private static final String USER = "system";
private static final String PASSWORD = "kingbase";
public static void main(String[] args) {
Connection connection = null;
PreparedStatement selectStmt = null;
PreparedStatement insertStmt = null;
PreparedStatement updateStmt = null;
try {
// 1. Establish database connection
connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);
// 2. Define the entity you want to save or update (e.g., IssueParticipant)
IssueParticipant participant = ...; // Assume this is your object with its properties set
// 3. Prepare SQL for checking existence by primary key
String selectSql = "SELECT * FROM issue_participant WHERE id = ?";
selectStmt = connection.prepareStatement(selectSql);
selectStmt.setLong(1, participant.getId()); // Assuming 'id' is the primary key field
// 4. Execute the select query and check if the record exists
ResultSet resultSet = selectStmt.executeQuery();
boolean recordExists = resultSet.next();
// 5. Perform the appropriate action based on existence
if (recordExists) {
// Record exists, so perform an update
String updateSql = "UPDATE issue_participant SET column1 = ?, column2 = ?, ... WHERE id = ?";
updateStmt = connection.prepareStatement(updateSql);
// Set update parameters based on your IssueParticipant object's fields
updateStmt.set...(...); // Set values for column1, column2, etc.
updateStmt.setLong(3, participant.getId());
int rowsUpdated = updateStmt.executeUpdate();
System.out.println(rowsUpdated + " row(s) updated.");
} else {
// Record does not exist, so perform an insert
String insertSql = "INSERT INTO issue_participant (column1, column2, ...) VALUES (?, ?, ...)";
insertStmt = connection.prepareStatement(insertSql);
// Set insert parameters based on your IssueParticipant object's fields
insertStmt.set...(...); // Set values for column1, column2, etc.
int rowsInserted = insertStmt.executeUpdate();
System.out.println(rowsInserted + " row(s) inserted.");
}
} catch (SQLException e) {
System.err.println("An error occurred while saving or updating the record:");
e.printStackTrace();
} finally {
// 6. Close resources
try {
if (selectStmt != null) {
selectStmt.close();
}
if (insertStmt != null) {
insertStmt.close();
}
if (updateStmt != null) {
updateStmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
System.err.println("Error closing resources:");
e.printStackTrace();
}
}
}
}
2.2 注意事项
在这个示例中使用了 java 语言中的基础语法。具体的代码功能如下。
- 首先建立与PostgreSQL数据库的连接。
- 定义要保存或更新的实体对象（此处以
IssueParticipant为例）。 - 准备SQL查询以根据主键检查记录是否存在。
- 执行查询并检查是否有结果返回。
- 根据记录存在与否，执行相应的插入或更新操作，并打印受影响的行数。
- 最后确保关闭所有数据库资源以防止资源泄露。
3 使用 ORM 框架进行开发
使用ORM（对象关系映射）框架如Hibernate、EclipseLink或Spring Data JPA等，可以大大简化数据库操作，包括实现 saveOrUpdate 操作。以下是使用Spring Data JPA和Hibernate作为ORM工具的示例。
3.1 实体类定义
首先，定义一个实体类，使用注解来标识与数据库表的映射关系。
import javax.persistence.*;
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true)
private String email;
private String username;
// 省略构造函数、getter和setter方法
}
3.2 存储库接口
Spring Data JPA 允许你通过扩展 JpaRepository 接口来自动实现标准的CRUD操作。
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
// 可以添加自定义查询方法，如果不添加自定义方法，Spring Data JPA会根据方法名称自动生成查询
}
3.3 配置Spring Data JPA
在Spring配置文件中配置数据源、JPA和事务管理器。
@Configuration
@EnableJpaAuditing
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = "com.example.repository",
entityManagerFactoryRef = "entityManagerFactory",
transactionManagerRef = "transactionManager"
)
public class JpaConfig {
// 数据源配置，省略具体实现
// JPA 配置，省略具体实现
// 配置EntityManagerFactory和TransactionManager，省略具体实现
}
3.4 实现 saveOrUpdate 操作
使用Spring Data JPA时，你不需要显式实现saveOrUpdate方法，因为JpaRepository已经提供了save方法，该方法内部会根据实体的ID属性判断是执行插入还是更新操作。
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public User saveOrUpdate(User user) {
return userRepository.save(user); // 这个方法会根据实体的ID属性决定是插入还是更新
}
}
3.5 注意事项
save方法在实体的ID为null时执行插入操作，在ID非null时执行更新操作。- 如果实体类使用了复合主键或需要特殊逻辑来确定记录的唯一性，可能需要自定义
saveOrUpdate方法。 - 在使用
Spring事务管理时，确保save操作在一个事务上下文中执行，以保持数据一致性。 - 确保已经配置了
Spring Data JPA的扫描路径，以便Spring能够扫描到你的实体类和存储库接口。
通过上述步骤，你可以使用Spring Data JPA和Hibernate轻松实现saveOrUpdate操作。ORM工具大大简化了与数据库的交互，减少了样板代码，并提高了开发效率。
merge 方法会检查对象是否已经存在于数据库中，然后根据需要执行插入或更新。
4 PLSQL 实现 UPSERT 操作
在 PostgreSQL 中可以实现 saveOrUpdate 功能。
- 第一：检查特定记录是否存在；
- 第二：根据这个检查结果来执行插入（INSERT）或更新（UPDATE）操作。
以下是使用 PL/SQL 实现 saveOrUpdate 的一个例子。
4.1 环境准备
- 创建一张
users表，并使用email为唯一键，使用id作为主键。
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(255),
last_updated TIMESTAMP
);
- 准备数据
INSERT INTO users (email, username)
VALUES ('user1@example.com', 'NewUsername'),('user2@example.com', 'NewUsername');
SELECT * FROM users;
id | email | username | last_updated
----+-------------------+-------------+--------------
1 | user1@example.com | NewUsername |
2 | user2@example.com | NewUsername |
(2 行记录)
4.2 创建函数
CREATE OR REPLACE FUNCTION save_or_update(p_id integer,p_email text)
RETURNS integer AS $
DECLARE
v_count integer;
BEGIN
-- 检查记录是否存在
SELECT COUNT(*) INTO v_count
FROM users
WHERE id = p_id;
-- 如果记录存在，则更新
IF v_count > 0 THEN
UPDATE users
SET email = p_email ,last_updated=now()
WHERE id = p_id;
-- 如果记录不存在，则插入新记录
ELSE
INSERT INTO users (id, email)
VALUES (p_id, p_email );
END IF;
-- 返回受影响的行数
RETURN v_count;
END;
$ LANGUAGE plpgsql;
4.3 调用存储过程
使用SELECT方式调用这个存储过程。
SELECT save_or_update(3, 'kingbase@kingbase.com.cn');
SELECT * FROM users;
- 第一次执行。
id | email | username | last_updated
----+--------------------------+-------------+--------------
2 | user2@example.com | NewUsername |
1 | kingbase@kingbase.com.cn | NewUsername |
(2 行记录)
- 第二次执行。
id | email | username | last_updated
----+--------------------------+-------------+----------------------------
1 | user1@example.com | NewUsername |
2 | user2@example.com | NewUsername |
3 | kingbase@kingbase.com.cn | | 2024-04-30 19:01:45.704983
(3 行记录)
Note
请多次执行此存储过程，并观察数据的变化情况。
第一次执行时，将会插入数据，时间字段为空。
第二次执行时，将会更新数据， last_updated 字段显示为当前时间戳。
4.4 注意事项
- 存储过程中的异常处理部分
EXCEPTION会捕获所有未捕获的异常，并执行回滚操作。
通过上述步骤，你可以使用 PL/SQL 在 PostgreSQL 数据库中实现 saveOrUpdate 操作。这种操作在动态执行插入或更新的场景中非常有用。
5 使用 PostgreSQL 数据库的 UPSERT 操作
在PostgreSQL中数据库系统提供了 UPSERT 操作，它可以在一行操作中同时执行插入和更新。，使用 ON CONFLICT 子句，可以实现此功能。
5.1 环境准备
- 创建一张
users表，并使用email为唯一键，使用id作为主键。
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(255) NOT NULL,
last_updated TIMESTAMP
);
- 准备数据
INSERT INTO users (email, username)
VALUES ('user1@example.com', 'NewUsername'),('user2@example.com', 'NewUsername');
SELECT * FROM users;
5.2 执行 UPSERT 操作
下面SQL 代码展示了如何在 PostgreSQL 中使用存储过程实现 saveOrUpdate 操作。通过使用 ON CONFLICT 子句，我们能够在一个原子操作中处理插入和更新，从而简化了应用层的逻辑。
INSERT INTO users (email, username)
VALUES ('user@example.com', 'NewUsername')
ON CONFLICT (email)
DO UPDATE SET
username = EXCLUDED.username,
last_updated = NOW();
5.3 验证 UPSERT 操作
SELECT * FROM users;
- 第一次执行。
id | email | username | last_updated
----+-------------------+-------------+--------------
1 | user1@example.com | NewUsername |
2 | user2@example.com | NewUsername |
3 | user@example.com | NewUsername |
(3 行记录)
- 第二次执行。
id | email | username | last_updated
----+-------------------+-------------+----------------------------
1 | user1@example.com | NewUsername |
2 | user2@example.com | NewUsername |
3 | user@example.com | NewUsername | 2024-04-30 19:03:20.213819
(3 行记录)
Note
请多次执行 UPSERT 操作，并观察数据的变化情况。
第一次执行时，将会插入数据，时间字段为空。
第二次执行时，将会更新数据， last_updated 字段显示为当前时间戳。
5.4 注意事项
在这个例子中，如果 user@example.com 这个邮箱已经存在于users 表中，那么它的 username 字段将被更新为 NewUsername，并且 last_updated字段将被设置为当前时间戳。
6 PLSQL 调用 UPSERT 操作
在PostgreSQL中，可以使用存储过程结合 INSERT ... ON CONFLICT 子句来实现 saveOrUpdate 功能。以下是一个完整的示例，展示如何创建一个存储过程来处理 saveOrUpdate 操作。
6.1 创建存储过程
接下来，我们创建一个存储过程 saveOrUpdateUser，它接受 email 和 username 作为参数，并根据 email 字段的值来决定是插入新记录还是更新现有记录。
CREATE OR REPLACE PROCEDURE saveOrUpdateUser(
IN p_email VARCHAR,
IN p_username VARCHAR
)
LANGUAGE plpgsql
AS $
BEGIN
-- 尝试插入新记录
INSERT INTO users (email, username)
VALUES (p_email, p_username)
ON CONFLICT (email) DO UPDATE
-- 如果发生冲突（即记录已存在），则更新 username
SET username = EXCLUDED.username,
last_updated = now();
-- 如果需要，可以在这里添加事务控制或其他逻辑
END;
$;
6.2 调用存储过程
在 Java 应用或其他使用 JDBC 的应用中，你可以通过以下方式调用这个存储过程：
CALL saveOrUpdateUser('kingbase1@kingbase.com.cn', 'kingbase');
SELECT * FROM users
- 第一次执行结果
id | email | username | last_updated
----+---------------------------+-------------+----------------------------
1 | user1@example.com | NewUsername |
2 | user2@example.com | NewUsername |
3 | user@example.com | NewUsername | 2024-04-30 19:03:20.213819
5 | kingbase1@kingbase.com.cn | kingbase |
(4 行记录)
- 第二次执行执行结果
id | email | username | last_updated
----+---------------------------+-------------+----------------------------
1 | user1@example.com | NewUsername |
2 | user2@example.com | NewUsername |
3 | user@example.com | NewUsername | 2024-04-30 19:03:20.213819
5 | kingbase1@kingbase.com.cn | kingbase | 2024-04-30 19:04:51.144720
(4 行记录)
Note
请多次调用存储过程，并观察数据的变化情况。
第一次执行时，将会插入数据，时间字段为空。
第二次执行时，将会更新数据， last_updated 字段显示为当前时间戳。
6.3 注意事项
- 存储过程可以大大简化应用层的逻辑，但也要谨慎使用，以避免将过多的逻辑放在数据库层面，这可能会使得应用变得难以维护。
- 根据你的具体需求，可能需要对存储过程进行适当的异常处理和优化。
7 小结 & FAQ
7.1 当数据库没有提供 UPSERT 功能时
这个时候尽量使用 ORM（对象关系映射）工具进行开发较为方便。
7.2 哪一种 saveOrUpdate 的方法更好呢？
上面已经对这些方法进行对比，目前可以得出一个结论。当使用 PostgreSQL 数据库时 UPSERT 功能时，可以大大提高应用软件开发的便捷性和稳定性。